﻿using SunshineAirlineTest1.DataAccess;
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;

namespace SunshineAirlineTest1
{
    public partial class SearchFlights : Form
    {
        private string sqltmp;
        private DataTable dttmp;

        private Dictionary<string, object> flightInfo;


        public SearchFlights()
        {
            InitializeComponent();
        }

        private void SearchFlights_Load(object sender, EventArgs e)
        {
            BindComboBoxData();
            cbDEPct.SelectedIndex = 4; cbARRct.SelectedIndex = 14;
            DateTime minDate = Convert.ToDateTime("2021-01-01");
            dateDEP.MinDate = minDate; dateRTN.MinDate = minDate;
            dateDEP.Value = Convert.ToDateTime("2021-07-01");
        }

        private void btnSearch_Click(object sender, EventArgs e)
        {
            if (cbDEPct.SelectedItem == null || string.IsNullOrEmpty(cbDEPct.SelectedValue.ToString())) { MessageBox.Show("No Such Flights."); return; }
            if (cbARRct.SelectedItem == null || string.IsNullOrEmpty(cbARRct.SelectedValue.ToString())) { MessageBox.Show("No Such Flights."); return; }
            DataTable directFlights, transferFlights;
            if (!searchFlights_Direct(out directFlights)) { MessageBox.Show("No Such Flights."); return; }
            if (!searchFlights_Transfer(out transferFlights)) { MessageBox.Show("No Such Flights."); return; }
            MessageBox.Show(GetDelayRateByRId(Convert.ToInt32(directFlights.Rows[0]["RouteId1"])).ToString() + "%");
        }

        #region Query

        private bool searchFlights_Direct(out DataTable dtout)
        {
            sqltmp = @"
            With queryDepartureFlights as ( select * from RouteWithCity where DepartureAirportIATA in ({0}) ),
            queryReturnFlights as ( select * from RouteWithCity where ArrivalAirportIATA in ({1}) ),
            queryDirectFlights as ( select d.* from queryDepartureFlights d inner join queryReturnFlights r on d.RouteId=r.RouteId )
            select df.RouteId RouteId1,
            DepartureAirportIATA,DepartureCityCode,DepartureCityName,ArrivalAirportIATA,ArrivalCityCode,ArrivalCityName,
            s.EconomyPrice s1_EconomyPrice,s.EconomyPrice*1.25 s1_BusinessPrice,s.EconomyPrice*1.5 s1_FirstPrice,
            s.ScheduleId s1_ScheduleId,s.AircraftId s1_AircraftId,s.FlightNumber s1_FlightNumber,
            s.DepartureDate s1_DepartureDate,s.ReturnDate s1_ReturnDate,0 walkMinutes,DATEDIFF(MINUTE,DepartureDate,ReturnDate) totalMinutes
            from queryDirectFlights df left join ScheduleWithDateTime s on s.RouteId=df.RouteId
            where DepartureDate>='{2}' order by DepartureDate";
            sqltmp = string.Format(sqltmp, cbDEPct.SelectedValue.ToString(), cbARRct.SelectedValue.ToString(), dateDEP.Value.ToString("yyyy-MM-dd"));
            dtout = SQLHelper.ExecuteDataTable(sqltmp);
            return dtout.Rows.Count > 0;
        }

        private bool searchFlights_Transfer(out DataTable dtout)
        {
            sqltmp = " select * from TransferFlights ";
            sqltmp += string.Format(" where DepartureAirportIATA in ({0}) and ArrivalAirportIATA in ({1}) ", cbDEPct.SelectedValue.ToString(), cbARRct.SelectedValue.ToString());
            if (dateDEP.Value > DateTime.MinValue) sqltmp += string.Format(" and s1_DepartureDate>='{0}' ", dateDEP.Value.ToString("yyyy-MM-dd"));
            dtout = SQLHelper.ExecuteDataTable(sqltmp);
            return dtout.Rows.Count > 0;
        }

        private List<Dictionary<string, object>> FillFlightInfo(List<Route> RouteList)
        {
            List<Dictionary<string, object>> rtnList = new List<Dictionary<string, object>>();
            foreach (Route r in RouteList)
            {
                sqltmp = "select * from Airport where IATACode='{0}'";
                r.DepartureAirport = SQLHelper.ReaderToModel<Airport>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.DepartureAirportIATA)));
                r.ArrivalAirport = SQLHelper.ReaderToModel<Airport>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.ArrivalAirportIATA)));
                sqltmp = "select * from City where CityCode='{0}'";
                r.DepartureAirport.City = SQLHelper.ReaderToModel<City>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.DepartureAirport.CityCode)));
                r.ArrivalAirport.City = SQLHelper.ReaderToModel<City>(SQLHelper.ExecuteReader(string.Format(sqltmp, r.ArrivalAirport.CityCode)));
                CreateDictionary();
                flightInfo["DepartureCity"] = r.DepartureAirport.City.CityName + "/" + r.DepartureAirportIATA;
                flightInfo["ArrivalCity"] = r.ArrivalAirport.City.CityName + "/" + r.ArrivalAirportIATA;
            }
            return rtnList;
        }

        private double GetDelayRateByRId(int routeId)
        {
            sqltmp = @" With Src as(
	                        select s.ScheduleId,DepartureDate,ReturnDate,ActualArrivalTime,DATEDIFF(MINUTE,ReturnDate,ActualArrivalTime) as TS
	                        from ScheduleWithDateTime s join FlightStatus fs on s.ScheduleId=fs.ScheduleId
	                        where s.RouteId={0} and  DATEDIFF(DAY,ActualArrivalTime,GETDATE()) between 0 and 30
                        )select case COUNT(0) when 0 then 100 else 100-ROUND((CONVERT(float,(select count(0) from Src where TS>0))/CONVERT(float,COUNT(0)))*100,2) end from Src ";
            sqltmp = string.Format(sqltmp, routeId);
            return SQLHelper.ExecuteScalar(sqltmp) == null ? 100f : Convert.ToDouble(SQLHelper.ExecuteScalar(sqltmp));
        }

        #endregion

        #region Tools
        private void BindComboBoxData()
        {
            sqltmp = "select CityName,stuff((select ','+char(39)+IATACode+char(39) from Airport a where c.CityCode=a.CityCode for xml path('')),1,1,'') as IATAs from City c order by CityName";
            dttmp = SQLHelper.ExecuteDataTable(sqltmp);
            if (dttmp.Rows.Count > 0)
            {
                CommonHelper.BindComboBoxList(cbDEPct, dttmp, "IATAs", "CityName");
                CommonHelper.BindComboBoxList(cbARRct, dttmp.Copy(), "IATAs", "CityName");
            }
            sqltmp = "select CabinTypeId,CabinTypeName from CabinType";
            dttmp = SQLHelper.ExecuteDataTable(sqltmp);
            if (dttmp.Rows.Count > 0) CommonHelper.BindComboBoxList(cbCabin, dttmp, "CabinTypeId", "CabinTypeName");
        }

        private void CreateDictionary()
        {
            flightInfo = new Dictionary<string, object>();
            string[] keys = new string[] {
                "TicketPrice","CabinType","FlightNum","DelayRate",
                "DepartureCity","ArrivalCity","DepatrureTime","ArrivalTime",
                "FlightType","TotalTime","RemainingTickets"
            };
            foreach (string key in keys)
            {
                flightInfo[key] = "";
            }
        }
        #endregion
    }
}
